# encoding: utf-8
__author__ = 'wwj'
import sys
reload(sys)
sys.setdefaultencoding('utf8')
import pymongo
import re
import MySQLdb
import MySQLdb.cursors
from datetime import *
import paramiko
# sys.path.append("/mnt/crawler/girl/dbShell/")
import conf
from conf import myException
from scrapy.mail import MailSender
import traceback
from collections import defaultdict,OrderedDict
from datetime import *
import redis
from pprint import pprint as pp
import sh
class dbshell(object):
    mailer = MailSender()
    sqlpath = None
    mongofrom = None
    urlpool = set()
    datas = []
    data = []
    orderItem = None
    exist = None
    myException = myException
    mongoTable = None
    where = {}
    exesize = 80
    urlpoolkey = None
    pool = redis.ConnectionPool(host='localhost', port=6379)
    redisconn = redis.Redis(connection_pool=pool)

    def __init__(self,spidername=''):
        self.spidername = spidername or self.spidername
        self.config = getattr(conf,spidername)
        # import ipdb;ipdb.set_trace()
        self.client = pymongo.MongoClient(self.config["from"].get('mongoIp'),socketKeepAlive=True,maxPoolSize=500)
        self.client.admin.authenticate(self.config["from"].get('mongoUser'), self.config["from"].get('mongoPwd'), mechanism='SCRAM-SHA-1')
        self.mongo = self.client[self.config.get('mongoDb')][self.mongoTable]
        # self.job = self.client[self.config.get('mongoDb')]["job"]
        self.sqlpath = '/Users/wwj/Downloads/%s.sql' % self.mysqlTable
        self.release_dir = '/tmp/%s.sql' % self.mongoTable
        # self.mongofrom = self.mongo.find(self.where).max_time_ms(1000000).sort("_id",-1)
        self.mongofrom = self.mongo.find(self.where,no_cursor_timeout=True).batch_size(self.exesize).sort("_id",1)
            # .sort("_id",-1).skip(216000)
        self.conn = MySQLdb.connect(self.config['release']['ip'],
        self.config['release']['mysqlUser'],self.config['release']['mysqlPwd'],
        self.config['mysqlDb'],charset="utf8mb4",cursorclass=MySQLdb.cursors.DictCursor,
        connect_timeout=100000000
        )
        self.conn.ping(True)
        self.cursor = self.conn.cursor()
        self.cursor.execute('set GLOBAL max_allowed_packet=67108864000000000')
        self.cursor.execute('set GLOBAL net_read_timeout=10000000')
        self.cursor.execute('set GLOBAL net_buffer_length=100000')
        self.cursor.execute('set GLOBAL net_write_timeout=10000000')
        self.cursor.execute('set GLOBAL wait_timeout=10000000')
        self.cursor.execute('set GLOBAL connect_timeout=10000000')


    def __call__(self):
        print "clean"
        self._clean()
        print "buildsql"
        # self.buildsql()
        print "insertdb"
        # self.insertdb()
        self.close()

    def _clean(self):
        for x in self.mongofrom:
        # while 1:
            # try:
            #     x = self.mongofrom.next()
            # except StopIteration:
            #     break
            # if not x:
            #     break
            self.data = OrderedDict(self.orderItem)
            print x.get("url")
            try:
                # x = self.mongofrom.next()
                tmp = ""
                if x.get("url",""):
                    tmp = x.get("url","").replace("https://www.cityheaven.net","")
                    tmp = tmp.replace("http://www.dmm.co.jp","")
                    tmp = tmp.replace("http://lovehotel-navi.jp","")
                if self.redisconn.sismember(self.mysqlTable + ":Url",tmp):
                    print "!!!!!!!!" + x.get("url","")
                    continue
                else:
                    if x.get("_id"):
                        del x["_id"]
                    print "########" + x.get("url","")
                    data = self.clean(x)

            except AttributeError,e:
                # import ipdb;ipdb.set_trace()
                self.job.insert({"date":str(date.today()),"msg":traceback.format_exc(),"name":self.spidername,"url":x["url"]})
                traceback.print_exc()
                continue
            except myException,e:
                import ipdb;ipdb.set_trace()
                traceback.print_exc()
                print e.message
            except Exception ,e:
                # import ipdb;ipdb.set_trace()
                # self.job.insert({"date":str(date.today()),"msg":traceback.format_exc(),"name":self.spidername,"url":x["url"]})
                traceback.print_exc()
                continue
                # log.msg(traceback.format_exc(),level=log.ERROR)
            else:
                if not x.get("url"):
                    import ipdb;ipdb.set_trace()
                # import ipdb;ipdb.set_trace()
                # self.datas.append(data)
                if len(self.datas) > self.exesize:
                    self.buildsql(self.datas[:self.exesize])

                self.redisconn.sadd(self.mysqlTable + ":Url",tmp)
        # else:
        #     self.job.insert({"date":str(date.today()),"msg":"no mongofrom","name":self.spidername})

    def buildsql(self,data):
        keys = self.data.keys()
        dd = []
        data_values = "(" + "%s," * (len(keys)) + ")"
        data_values = data_values.replace(',)', ')')
        keys = str(tuple(keys)).replace("'",'')
        values = [data]
        for value in values:
            dd = []
            sql = "insert ignore into %s %s values %s" % (self.mysqlTable,keys,data_values)
            # sql = "replace into %s %s values %s" % (self.mysqlTable,keys,data_values)
            # sql = "INSERT INTO auto %s(%s) VALUES (%s) ON DUPLICATE KEY UPDATE urlHash=VALUES(urlHash)"
            for v in value:
                dd.append([MySQLdb.escape_string(str(y).strip().replace("%","")) for yy,y in v.iteritems()])
            try:
                pp(dd)
                self.conn.ping(True)
                self.cursor = self.conn.cursor()
                self.cursor.executemany(sql,dd)
                self.conn.commit()
                del self.datas[:self.exesize]
            except Exception as ex:
                # import ipdb;ipdb.set_trace()
                self.conn.ping(True)
                print ex
                # code, message = ex.args
                # m = re.search(r".* at row (\d+)$", message)



    def getdb(self,sql):
        cursor = self.conn.cursor()
        cursor.execute(sql)
        tmp = cursor.fetchallDict()
        return tmp
    # 生成批量入库sql文件

    def geturltool(self,sql):
        # urldb = self.client[self.config.get('mongoDb')][self.spidername+"url"]
        # self.urltool = urldb.find({},{"url":1})
        # if self.urltool.count() == 0:
        tmp = self.getdb(sql)
        self.redisconn.sadd(self.mysqlTable + ":Url",*map(lambda a:a.get("url").strip(),tmp))
        # urldb.insert_many([{'url': i} for i in tmp])
        # self.urltool = urldb.find({},{"url":1})
        # self.urltool = [x["url"] for x in self.urltool]

    def close(self):
        self.cursor.close()
        self.conn.commit()
